﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using Microsoft.ServiceBus.Messaging;
using DbToPbiSvc.Logging;
using Newtonsoft.Json;
using System.Configuration;
using System.Threading;

namespace DbToPbiSvc
{
    public class Worker
    {
        public Query Query { get; set; }

        public void WorkerProcedure()
        {
            try
            {
                LogHelper.TraceDebugInfo("IN");

                DateTime startTime = DateTime.Now.AddSeconds(-5);
                string refreshToken = string.Empty;

                while (!Main.ServiceIsStopped)
                {

                    if ((DateTime.Now - startTime).TotalSeconds >= Query.RefreshInterval)
                    {

                        var sqlStatement = GetStatement();
                        var dataTable = GetData(sqlStatement);
                        var json = JsonConvert.SerializeObject(dataTable);

                        if (ConfigurationManager.AppSettings["DatasetTraceEnabled"].ToUpper() == "TRUE")
                        {
                            LogHelper.TraceDebugInfo(json);
                        }

                        if (Query.ConnectTo.ToLower() == "powerbiapi")
                        {

                            // Authentication
                            string token = PowerBi.Helper.GetAccessToken(Query.PowerBiApi.ClientId,
                                Query.PowerBiApi.UserName, Query.PowerBiApi.Password, ref refreshToken);

                            if (!string.IsNullOrEmpty(token))
                            {

                                // Clear Recordset
                                if (Query.PowerBiApi.ClearRecordset)
                                {
                                    PowerBi.Helper.EmptyDataset(token, Query.PowerBiApi.DatasetId,
                                        Query.PowerBiApi.TableName).Wait();
                                }

                                // Add rows
                                PowerBi.Helper.AddRows(token, Query.PowerBiApi.DatasetId, Query.PowerBiApi.TableName,
                                    json).Wait();
                            }

                        }
                        else
                        {
                            SendToAzure(json);
                        }

                        startTime = DateTime.Now;
                    }
                    else
                    {
                        Thread.Sleep(200);
                    }

                }

            }
            catch (Exception exc)
            {
                LogHelper.TraceError(exc);
            }
            finally
            {
                LogHelper.TraceDebugInfo("OUT");
            }
        }

        private string GetStatement()
        {

            string returnValue = string.Empty;

            try
            {

                LogHelper.TraceDebugInfo("IN");

                var sqlStatementPath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);

                if (sqlStatementPath != null)
                {
                    returnValue = File.ReadAllText(Path.Combine(sqlStatementPath, Query.QueryPath));
                }

            }
            catch (Exception exc)
            {
                LogHelper.TraceError(exc);
            }
            finally
            {
                LogHelper.TraceDebugInfo("OUT");
            }

            return returnValue;

        }

        private DataTable GetData(string commandText)
        {

            DbConnection currentConnection = null;
            DataTable returnValue = new DataTable();

            try
            {

                LogHelper.TraceDebugInfo("IN - Retrieving data from database.");

                using (currentConnection = new SqlConnection(Query.DatabaseConnectionString))
                {

                    currentConnection.Open();

                    using (DbCommand command = currentConnection.CreateCommand())
                    {
                        command.CommandText = commandText;
                        command.CommandType = CommandType.Text;
                        var dataReader = command.ExecuteReader();

                        returnValue.Load(dataReader);

                        currentConnection.Close();
                    }
                }

            }
            catch (Exception exc)
            {
                LogHelper.TraceError(exc);
            }
            finally
            {
                if (currentConnection != null)
                {
                    if (currentConnection.State == ConnectionState.Open)
                    {
                        currentConnection.Close();
                    }
                    currentConnection.Dispose();
                }

                LogHelper.TraceDebugInfo("OUT");
            }

            return returnValue;
        }

        private void SendToAzure(string data)
        {

            try
            {

                LogHelper.TraceDebugInfo("IN - Sending data to Azure.");

                var eventHubClient = EventHubClient.CreateFromConnectionString(Query.EventHub.ConnectionString, Query.EventHub.QueueName);
                eventHubClient.Send(new EventData(Encoding.UTF8.GetBytes(data)));
                eventHubClient.Close();

            }
            catch (Exception exc)
            {
                LogHelper.TraceError(exc);
            }
            finally
            {
                LogHelper.TraceDebugInfo("OUT");
            }

        }
    }
}
